import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_good_inv_router = APIRouter(prefix="/material/goodInv", tags=["物料库存管理"],
                                 dependencies=[Depends(oauth2_scheme)])


class MaterialInventorySearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fmonth: Optional[str] = Field("", description="统计月份(yyyyMM)")
    fstockno: Optional[str] = Field("", description="仓库编号")
    fgoodname: Optional[str] = Field("", description="物料名称（支持前缀匹配）")


@data_good_inv_router.get("/list", summary="物料库存查询接口")
def list_good_inv(search: MaterialInventorySearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_tc_good_inv a,i_ts_good_info b,lims.i_ts_stock c"
    sql = sql + " where 1=1"
    sql = sql + " and a.fsite_no=b.fsite_no and a.fgoodno=b.fgoodno"
    sql = sql + " and a.fsite_no=c.fsite_no and a.fstockno=c.fstockno"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fmonth != "":
        sql = sql + " and a.fmonth = :fmonth "
        param["fmonth"] = search.fmonth
    if search.fstockno != "":
        sql = sql + " and a.fstockno = :fstockno "
        param["fstockno"] = search.fstockno
    if search.fgoodname != "":
        sql = sql + " and b.fgoodname like concat('%', :fgoodname ,'%')"
        param["fgoodname"] = search.fgoodname
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fmonth,c.fstockname,b.fgoodno,b.fgoodname,b.fspec,b.fordunit"
    sql = sql + ",a.fbegqty,a.frecqty,a.fretqty,a.fmovqty,a.foutqty,finqty,a.fuseqty"
    sql = sql + ",a.fbalqty,a.fbalqty*coalesce(b.fordpack,1) repertory"
    sql = sql + ",a.fmrkqty,c.fstockno"
    sql = sql + " from i_tc_good_inv a,i_ts_good_info b,lims.i_ts_stock c"
    sql = sql + " where 1=1"
    sql = sql + " and a.fsite_no=b.fsite_no and a.fgoodno=b.fgoodno"
    sql = sql + " and a.fsite_no=c.fsite_no and a.fstockno=c.fstockno"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fmonth != "":
        sql = sql + " and a.fmonth = :fmonth "
        param["fmonth"] = search.fmonth
    if search.fstockno != "":
        sql = sql + " and a.fstockno = :fstockno "
        param["fstockno"] = search.fstockno
    if search.fgoodname != "":
        sql = sql + " and b.fgoodname like concat('%', :fgoodname ,'%')"
        param["fgoodname"] = search.fgoodname
    sql = sql + " order by  a.fmonth,c.fstockname,b.fgoodno"
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fmonth'] = data[key][0]
        d1['fstockname'] = data[key][1]
        d1['fgoodno'] = data[key][2]
        d1['fgoodname'] = data[key][3]
        d1['fspec'] = data[key][4]
        d1['fordunit'] = data[key][5]
        d1['fbegqty'] = float(data[key][6])
        d1['frecqty'] = float(data[key][7])
        d1['fretqty'] = float(data[key][8])
        d1['fmovqty'] = float(data[key][9])
        d1['foutqty'] = float(data[key][10])
        d1['finqty'] = float(data[key][11])
        d1['fuseqty'] = float(data[key][12])
        d1['fbalqty'] = float(data[key][13])
        d1['repertory'] = float(data[key][14])
        d1['fmrkqty'] = float(data[key][15])
        d1['fstockno'] = data[key][16]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class MaterialInventoryDetailSearch(BaseModel):
    """物料库存明细查询请求参数"""
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fmonth: Optional[str] = Field("", description="统计月份(yyyyMM)")
    fstockno: Optional[str] = Field("", description="仓库编号")
    fgoodno: Optional[str] = Field("", description="物料编号")


@data_good_inv_router.get("/docalgoodinvdtl", summary="物料库存明细查询接口")
def list_docal_good(search: MaterialInventoryDetailSearch, db: Session = Depends(get_db)):
    sql = "select * from proc_cab_docalgoodinvdtl( :fmonth , :fstockno , :fgoodno , :fsiteNo );"
    data = db.execute(text(sql), {
        "fmonth": search.fmonth,
        "fstockno": search.fstockno,
        "fgoodno": search.fgoodno,
        "fsiteNo": search.fsiteNo,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbizname'] = data[key][0]
        d1['fbizno'] = data[key][1]
        d1['fbizdate'] = data[key][2]
        d1['f1'] = data[key][3]
        d1['f2'] = data[key][4]
        d1['fmemo'] = data[key][5]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)
